package com.bilibili.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;

import com.bilibili.dao.BaseDao;
import com.bilibili.dao.VideoDao;
import com.bilibili.entity.Video;

public class VideoDaoImpl implements VideoDao {

	@Override
	public Video selectVideoById(int videoId) {
		Connection conn = BaseDao.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Video video = null;
		try {
			String sql = "SELECT * FROM video WHERE video_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, videoId);
			rs = ps.executeQuery();
			if (rs.next()) {
				video = new Video();
				video.setVideoId(rs.getInt("video_id"));
				video.setTitle(rs.getString("title"));
				video.setDescription(rs.getString("description"));
				video.setCoverUrl(rs.getString("cover_url"));
				video.setVideoUrl(rs.getString("video_url"));
				video.setCategoryId(rs.getInt("category_id"));
				video.setUploaderId(rs.getInt("uploader_id"));
				video.setCreateTime(rs.getDate("create_time"));
				video.setDuration(rs.getInt("duration"));
				video.setStatus(rs.getInt("status"));
				video.setViewCount(rs.getInt("view_count"));
				video.setLikeCount(rs.getInt("like_count"));
				video.setCoinCount(rs.getInt("coin_count"));
				video.setCollectCount(rs.getInt("collect_count"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(conn, ps, rs);
		}
		return video;
	}

	@Override
	public List<Video> selectVideosByIds(List<Integer> videoIds) {
		if (videoIds.isEmpty())
			return new ArrayList<>();

		Connection conn = BaseDao.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Video> videos = new ArrayList<>();
		try {
			// 构建IN条件
			StringBuilder sql = new StringBuilder("SELECT * FROM video WHERE video_id IN (");
			for (int i = 0; i < videoIds.size(); i++) {
				sql.append("?");
				if (i < videoIds.size() - 1) {
					sql.append(", ");
				}
			}
			sql.append(")");

			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < videoIds.size(); i++) {
				ps.setInt(i + 1, videoIds.get(i));
			}
			rs = ps.executeQuery();

			while (rs.next()) {
				Video video = new Video();
				video.setVideoId(rs.getInt("video_id"));
				video.setTitle(rs.getString("title"));
				video.setDescription(rs.getString("description"));
				video.setCoverUrl(rs.getString("cover_url"));
				video.setVideoUrl(rs.getString("video_url"));
				video.setCategoryId(rs.getInt("category_id"));
				video.setUploaderId(rs.getInt("uploader_id"));
				video.setCreateTime(rs.getDate("create_time"));
				video.setDuration(rs.getInt("duration"));
				video.setStatus(rs.getInt("status"));
				video.setViewCount(rs.getInt("view_count"));
				video.setLikeCount(rs.getInt("like_count"));
				video.setCoinCount(rs.getInt("coin_count"));
				video.setCollectCount(rs.getInt("collect_count"));
				videos.add(video);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(conn, ps, rs);
		}
		return videos;
	}

	@Override
	public int insertVideo(Video video) {
		String sql = "INSERT INTO video(title,description,cover_url,video_url,category_id,uploader_id,create_time,duration,status,view_count,like_count,coin_count,collect_count) "
				+ "VALUES(?,?,?,?,?,?,now(),?,?,0,0,0,0)";
		Object[] param = { video.getTitle(), video.getDescription(), video.getCoverUrl(), video.getVideoUrl(),
				video.getCategoryId(), video.getUploaderId(), video.getDuration(), video.getStatus() };
		int num = BaseDao.executeUpdate(sql, param);
		return num;
	}

	@Override
	public List<Video> selectByAll(int uploaderId) {
		Connection conn = BaseDao.getConn();// 打开连接
		String sql = "SELECT * FROM video WHERE uploader_id = ?";
		List<Video> list = new ArrayList<Video>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, uploaderId);
			rs = ps.executeQuery();
			while (rs.next()) {
				Video v = new Video();
				v.setVideoId(rs.getInt("video_id"));
				v.setTitle(rs.getString("title"));
				v.setDescription(rs.getString("description"));
				v.setCoverUrl(rs.getString("cover_url"));
				v.setVideoUrl(rs.getString("video_url"));
				v.setCategoryId(rs.getInt("category_id"));
				v.setUploaderId(rs.getInt("uploader_id"));
				v.setCreateTime(rs.getDate("create_time"));
				v.setDuration(rs.getInt("duration"));
				v.setStatus(rs.getInt("status"));
				v.setViewCount(rs.getInt("view_count"));
				v.setLikeCount(rs.getInt("like_count"));
				v.setCoinCount(rs.getInt("coin_count"));
				v.setCollectCount(rs.getInt("collect_count"));
				list.add(v);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(conn, ps, rs);
		}
		return list;
	}

	@Override
	public int selectAllViewByUid(int uploaderId) {
		Connection conn = BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT SUM(view_count) AS total FROM video WHERE uploader_id = ?";
			s = conn.prepareStatement(sql);
			s.setInt(1, uploaderId);
			rs = s.executeQuery();
			if (rs.next()) {
				return rs.getInt("total");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(conn, s, rs);
		}
		return 0;
	}

	@Override
	public int selectViewByUidAndDate(int uploaderId, Date nowDate) {
		Connection conn = BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			// 注意：这里需要正确的日期格式处理，假设使用java.sql.Date
			java.sql.Date sqlDate = new java.sql.Date(nowDate.getTime());
			String sql = "SELECT SUM(view_count) AS total FROM video WHERE uploader_id = ? AND DATE(create_time) = ?";
			s = conn.prepareStatement(sql);
			s.setInt(1, uploaderId);
			s.setDate(2, sqlDate);
			rs = s.executeQuery();
			if (rs.next()) {
				return rs.getInt("total");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(conn, s, rs);
		}
		return 0;
	}

	@Override
	public Video getVideoById(int videoId) {
	    Connection conn = BaseDao.getConn();
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    Video video = null;
	    try {
	        String sql = "SELECT * FROM video WHERE video_id = ?";
	        ps = conn.prepareStatement(sql);
	        ps.setInt(1, videoId);
	        rs = ps.executeQuery();
	        if (rs.next()) {
	            video = new Video();
	            video.setVideoId(rs.getInt("video_id"));
	            video.setTitle(rs.getString("title"));
	            video.setDescription(rs.getString("description"));
	            video.setCoverUrl(rs.getString("cover_url"));
	            video.setVideoUrl(rs.getString("video_url"));
	            video.setCategoryId(rs.getInt("category_id"));
	            video.setUploaderId(rs.getInt("uploader_id"));
	            video.setCreateTime(rs.getDate("create_time"));
	            video.setDuration(rs.getInt("duration"));
	            video.setStatus(rs.getInt("status"));
	            video.setViewCount(rs.getInt("view_count"));
	            video.setLikeCount(rs.getInt("like_count"));
	            video.setCoinCount(rs.getInt("coin_count"));
	            video.setCollectCount(rs.getInt("collect_count"));
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(conn, ps, rs);
	    }
	    return video;
	}
	public int deleteVideosByVideoId(int user_id, int VideoId) {
		// TODO Auto-generated method stub
		String sql = "update video set status = 2 where uploader_id = ? and video_id = ?";
		Object[] param = { user_id,VideoId };
		return BaseDao.executeUpdate(sql, param);
	}
	@Override
	public List<Video> selectAllVideo(Video v,int pageNum,int pageSize) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		List<Object> queryList = new ArrayList<Object>();
		String sql = "SELECT * FROM video WHERE 1 = 1 ";
		if(v.getStatus() != -1) {
			sql+="AND status = ? ";
			queryList.add(v.getStatus());
		}
		if(v.getTitle() != null && !"".equals(v.getTitle())) {
			sql+="AND title like concat('%',?,'%') ";
			queryList.add(v.getTitle());
		}
		sql+="LIMIT ?,?";
		List<Video> list = new ArrayList<Video>();
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			for(int i = 0;i<queryList.size();i++) {
				ps.setObject(i+1, queryList.get(i));
			}
			ps.setInt(queryList.size()+1, (pageNum-1)*pageSize);
			ps.setInt(queryList.size()+2, pageSize);
			rs = ps.executeQuery();			
			while(rs.next()) {
				Video video = new Video();
				video.setVideoId(rs.getInt("video_id"));
				video.setTitle(rs.getString("title"));
				video.setDescription(rs.getString("description"));
				video.setCoverUrl(rs.getString("cover_url"));
				video.setVideoUrl(rs.getString("video_url"));
				video.setCategoryId(rs.getInt("category_id"));
				video.setUploaderId(rs.getInt("uploader_id"));
				video.setCreateTime(rs.getDate("create_time"));
				video.setDuration(rs.getInt("duration"));
				video.setStatus(rs.getInt("status"));
				video.setViewCount(rs.getInt("view_count"));
				video.setLikeCount(rs.getInt("like_count"));
				video.setCoinCount(rs.getInt("coin_count"));
				video.setCollectCount(rs.getInt("collect_count"));
			    list.add(video);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return list;
	}

	@Override
	public int selectAllVideoNum(Video v) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		List<Object> queryList = new ArrayList<Object>();
		String sql = "SELECT count(1) as total FROM video WHERE 1 = 1 ";
		if(v.getStatus() != -1) {
			sql+="AND status = ? ";
			queryList.add(v.getStatus());
		}
		if(v.getTitle() != null && !"".equals(v.getTitle())) {
			sql+="AND title like concat('%',?,'%') ";
			queryList.add(v.getTitle());
		}
		PreparedStatement ps= null;
		ResultSet rs=null;
		int row = 0;
		try {
			ps= conn.prepareStatement(sql);
			for(int i = 0;i<queryList.size();i++) {
				ps.setObject(i+1, queryList.get(i));
			}
			rs = ps.executeQuery();			
			if(rs.next()) {
			    row = rs.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return row;
	}

	@Override
	public int selectNotExamineVideoNum() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		String sql = "SELECT count(1) as total FROM video WHERE status = 0 ";
		PreparedStatement ps= null;
		ResultSet rs=null;
		int row = 0;
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();			
			if(rs.next()) {
			    row = rs.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return row;
	}

	@Override
	public int updateVideostate(Video v) {
		// TODO Auto-generated method stub
		String sql = "UPDATE video SET status = ? WHERE video_id = ?";
		Object[] param = {v.getStatus(),v.getVideoId()};
		return BaseDao.executeUpdate(sql, param);
	}

	@Override
	public int selectAllVideoNum() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		String sql = "SELECT count(1) as total FROM video";
		PreparedStatement ps= null;
		ResultSet rs=null;
		int row = 0;
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();			
			if(rs.next()) {
			    row = rs.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return row;
	}
	
	

	@Override
	public int updateVideoStateByVideoId(int state, int videoId) {
		// TODO Auto-generated method stub
		String sql = "update video set status = ? where video_id = ?";
		Object[] param = { state,videoId };
		return BaseDao.executeUpdate(sql, param);
	}

	@Override
	public String selectVideoTitleStateByVideoId(int videoId) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		String sql = "SELECT * FROM video WHERE video_id = ? ";
		String title = "";
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			ps.setInt(1,videoId);
			rs = ps.executeQuery();			
			if(rs.next()) {
				title = rs.getString("title");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return title;
	}

	@Override
	public int addVideoviewCount(int videoId) {
		// TODO Auto-generated method stub
		String sql = "update video set view_count = view_count+1 where video_id = ?";
		Object[] param = { videoId };
		return BaseDao.executeUpdate(sql, param);
	}
}